using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;

namespace Pegasus.Data.SqlClient
{
	/// <summary>
	/// 
	/// </summary>
	/// <typeparam name="TMappedType"></typeparam>
	public partial class SqlObjectMapper< TMappedType >
		where TMappedType : new()
	{
		// Local Instance Values
		private object m_lock = new object();
		private SqlDatabaseConnection m_databaseConnection;
		private Dictionary<string, FieldInfo> m_objectFields = new Dictionary<string, FieldInfo>();
		private Dictionary<string, PropertyInfo> m_objectProperties = new Dictionary<string, PropertyInfo>();
		private Dictionary<string, List<ColumnMap>> m_mappingTable = new Dictionary<string, List<ColumnMap>>();

		/// <summary>
		/// Initializes a new instance of the <see cref="T:SqlObjectMapper&lt;TMappedType&gt;"/> class.
		/// </summary>
		/// <param name="databaseConnection">The database connection.</param>
		public SqlObjectMapper( SqlDatabaseConnection databaseConnection )
		{
			m_databaseConnection = databaseConnection;
			BuildObjectMap();
		}

		/// <summary>
		/// Gets the SQL database connection.
		/// </summary>
		/// <value>The SQL database connection.</value>
		public SqlDatabaseConnection SqlDatabaseConnection
		{
			get
			{
				return m_databaseConnection;
			}
		}

		/// <summary>
		/// Gets the mapped object.
		/// </summary>
		/// <param name="sqlFormat">The SQL format.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public List<TMappedType> ExecuteSql( string sqlFormat, params object[] args )
		{
			using( SqlDatabaseAccess access = m_databaseConnection.CreateDatabaseAccess() )
			{
				return ExecuteSql( access, sqlFormat, args );
			}
		}

		/// <summary>
		/// Gets the mapped object.
		/// </summary>
		/// <param name="sqlFormat">The SQL format.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public TMappedType ExecuteSqlEx( string sqlFormat, params object[] args )
		{
			List<TMappedType> theList = ExecuteSql( sqlFormat, args );
			if( theList.Count == 1 )
			{
				return theList[ 0 ];
			}

			return default( TMappedType );
		}

		/// <summary>
		/// Gets the mapped object.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="sqlFormat">The SQL format.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public List<TMappedType> ExecuteSql( SqlDatabaseAccess access, string sqlFormat, params object[] args )
		{
			string sql = string.Format( sqlFormat, args );

			// If this is the first time we have seen this sql we have to 
			// lock the object while the column map is being built
			lock( m_lock )
			{
				if( !m_mappingTable.ContainsKey( sql ) )
				{
					return BuildColumnMapForSql( access, sql );
				}
			}

			return ExecuteSql( access, m_mappingTable[ sql ], sql );
		}

		/// <summary>
		/// Gets the mapped object.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="sqlFormat">The SQL format.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public TMappedType ExecuteSqlEx( SqlDatabaseAccess access, string sqlFormat, params object[] args )
		{
			List<TMappedType> theList = ExecuteSql( access, sqlFormat, args );
			if( theList.Count == 1 )
			{
				return theList[ 0 ];
			}

			return default( TMappedType );
		}

		/// <summary>
		/// Gets the mapped object stored proc.
		/// </summary>
		/// <param name="storedProc">The stored proc.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public List<TMappedType> ExecuteStoredProc( string storedProc, params object[] args )
		{
			using( SqlDatabaseAccess access = m_databaseConnection.CreateDatabaseAccess() )
			{
				return ExecuteStoredProc( access, storedProc, args );
			}
		}

		/// <summary>
		/// Gets the mapped object stored proc.
		/// </summary>
		/// <param name="storedProc">The stored proc.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public TMappedType ExecuteStoredProcEx( string storedProc, params object[] args )
		{
			List<TMappedType> theList = ExecuteStoredProc( storedProc, args );
			if( theList.Count == 1 )
			{
				return theList[ 0 ];
			}

			return default( TMappedType );
		}

		/// <summary>
		/// Gets the mapped object stored proc.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="storedProc">The stored proc.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public List<TMappedType> ExecuteStoredProc( SqlDatabaseAccess access, string storedProc, params object[] args )
		{
			// If this is the first time we have seen this sql we have to 
			// lock the object while the column map is being built
			lock( m_lock )
			{
				if( !m_mappingTable.ContainsKey( storedProc ) )
				{
					return BuildColumnMapForStoredProc( access, storedProc, args );
				}
			}

			return ExecuteStoredProc( access, m_mappingTable[ storedProc ], storedProc, args );
		}

		/// <summary>
		/// Gets the mapped object stored proc.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="storedProc">The stored proc.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		public TMappedType ExecuteStoredProcEx( SqlDatabaseAccess access, string storedProc, params object[] args )
		{
			List<TMappedType> theList = ExecuteStoredProc( access, storedProc, args );
			if( theList.Count == 1 )
			{
				return theList[ 0 ];
			}

			return default( TMappedType );
		}

		/// <summary>
		/// Builds the object map.
		/// </summary>
		private void BuildObjectMap()
		{
			Type objectType = typeof( TMappedType );
			
			// Make sure that the object has a default constructor
			ConstructorInfo ctor = objectType.GetConstructor( new Type[ 0 ] );
			if( ctor == null )
			{
				throw new Exception( string.Format( "Class {0} must have a default constructor defined to work with the object mapping component.", objectType.Name ) );
			}

			FieldInfo[] fields = objectType.GetFields( BindingFlags.Instance | BindingFlags.Public );
			foreach( FieldInfo fieldInfo in fields )
			{
				if( !fieldInfo.IsInitOnly && !fieldInfo.IsLiteral )
				{
					string fieldName = fieldInfo.Name.ToLower();

					object[] mapAttribs = fieldInfo.GetCustomAttributes( typeof( SqlObjectMapAttribute ), false );
					if( mapAttribs.Length > 0 )
					{
						fieldName = ( (SqlObjectMapAttribute) mapAttribs[ 0 ] ).ColumnName.ToLower();
					}

					m_objectFields.Add( fieldName, fieldInfo );
				}
			}

			PropertyInfo[] properties = objectType.GetProperties( BindingFlags.Instance | BindingFlags.Public );
			foreach( PropertyInfo propInfo in properties )
			{
				if( propInfo.CanWrite )
				{
					string propName = propInfo.Name.ToLower();

					object[] mapAttribs = propInfo.GetCustomAttributes( typeof( SqlObjectMapAttribute ), false );
					if( mapAttribs.Length > 0 )
					{
						propName = ( (SqlObjectMapAttribute) mapAttribs[ 0 ] ).ColumnName.ToLower();
					}

					m_objectProperties.Add( propName, propInfo );
				}
			}
		}

		/// <summary>
		/// Executes the SQL.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="columnMapList">The column map list.</param>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		private List<TMappedType> ExecuteSql( SqlDatabaseAccess access, List<ColumnMap> columnMapList, string sql )
		{
			using( SqlDataReader reader = access.SqlReader( sql ) )
			{
				return BuildTypedObjects( columnMapList, reader );
			}
		}

		/// <summary>
		/// Executes the stored proc.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="columnMapList">The column map list.</param>
		/// <param name="storedProc">The stored proc.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		private List<TMappedType> ExecuteStoredProc( SqlDatabaseAccess access, List<ColumnMap> columnMapList, string storedProc, params object[] args )
		{
			using( SqlDataReader reader = access.StoredProcReader( storedProc, args ) )
			{
				return BuildTypedObjects( columnMapList, reader );
			}
		}

		/// <summary>
		/// Builds the column map SQL.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="sql">The SQL.</param>
		/// <returns></returns>
		private List<TMappedType> BuildColumnMapForSql( SqlDatabaseAccess access, string sql )
		{
			DataTable dataTable = access.SqlDataTable( sql );
			List<ColumnMap> columnMapList = BuildColumnMap( sql, dataTable );
			return BuildTypedObjects( columnMapList, dataTable );
		}

		/// <summary>
		/// Builds the column mapping stored proc.
		/// </summary>
		/// <param name="access">The access.</param>
		/// <param name="storedProc">The stored proc.</param>
		/// <param name="args">The args.</param>
		/// <returns></returns>
		private List<TMappedType> BuildColumnMapForStoredProc( SqlDatabaseAccess access, string storedProc, params object[] args )
		{
			DataTable dataTable = access.StoredProcDataTable( storedProc, args );

			List<ColumnMap> columnMapList = BuildColumnMap( storedProc, dataTable );
			return BuildTypedObjects( columnMapList, dataTable );
		}

		/// <summary>
		/// Builds the stored proc.
		/// </summary>
		/// <param name="sqlName">Name of the SQL.</param>
		/// <param name="dataTable">The data table.</param>
		/// <returns></returns>
		private List<ColumnMap> BuildColumnMap( string sqlName, DataTable dataTable )
		{
			// Build the mappings between the object properties/files and columns in the datatable.
			int columnCount = dataTable.Columns.Count;
			List<ColumnMap> columnMapList = new List<ColumnMap>();

			for( int x = 0; x < columnCount; x++ )
			{
				// Get the column information
				DataColumn column = dataTable.Columns[ x ];
				Type columnType = column.DataType;
				TypeConverter columnConverter = TypeDescriptor.GetConverter( columnType );
				string columnName = column.ColumnName.ToLower();

				// Does this match one of the files or properties on the mapped object type.
				if( m_objectFields.ContainsKey( columnName ) )
				{
					// Get the field info
					FieldInfo fieldInfo = m_objectFields[ columnName ];
					Type fieldType = fieldInfo.FieldType;

					bool sameType = fieldType.Equals( columnType );
					if( sameType || columnConverter.CanConvertTo( fieldType ) )
					{
						// Ok, we can convert the column to the field
						ColumnMap columnMap = new ColumnMap();
						columnMap.ColumnNumber = x;
						columnMap.MapType = ColumnMapType.ToField;
						columnMap.Converter = sameType ? null : columnConverter;
						columnMap.ToType = fieldType;
						columnMap.Field = fieldInfo;

						columnMapList.Add( columnMap );
					}
				}
				else if( m_objectProperties.ContainsKey( columnName ) )
				{
					PropertyInfo propInfo = m_objectProperties[ columnName ];
					Type propType = propInfo.PropertyType;

					bool sameType = propType.Equals( columnType );
					if( sameType || columnConverter.CanConvertTo( propType ) )
					{
						// Ok, we can convert the column to the property
						ColumnMap columnMap = new ColumnMap();
						columnMap.ColumnNumber = x;
						columnMap.MapType = ColumnMapType.ToProperty;
						columnMap.Converter = sameType ? null : columnConverter;
						columnMap.ToType = propType;
						columnMap.Property = propInfo;

						columnMapList.Add( columnMap );
					}
				}
			}

			m_mappingTable.Add( sqlName, columnMapList );

			return columnMapList;
		}

		/// <summary>
		/// Builds the type of the mapped.
		/// </summary>
		/// <param name="columnMapList">The column map list.</param>
		/// <param name="dataTable">The data table.</param>
		/// <returns></returns>
		private List<TMappedType> BuildTypedObjects( List<ColumnMap> columnMapList, DataTable dataTable )
		{
			List<TMappedType> mappedList = new List<TMappedType>();

			foreach( DataRow row in dataTable.Rows )
			{
				TMappedType mappedObject = new TMappedType();

				foreach( ColumnMap columnMap in columnMapList )
				{
					// Get the value and set it in the proper field/property
					object value = row[ columnMap.ColumnNumber ];
					SetObjectValue( mappedObject, columnMap, value );
				}

				mappedList.Add( mappedObject );
			}

			return mappedList;
		}

		/// <summary>
		/// Builds the typed objects.
		/// </summary>
		/// <param name="columnMapList">The column map list.</param>
		/// <param name="reader">The reader.</param>
		/// <returns></returns>
		private List<TMappedType> BuildTypedObjects( List<ColumnMap> columnMapList, SqlDataReader reader )
		{
			List<TMappedType> mappedList = new List<TMappedType>();

			while( reader.Read() )
			{
				TMappedType mappedObject = new TMappedType();

				foreach( ColumnMap columnMap in columnMapList )
				{
					// Get the value and set it in the proper field/property
					object value = reader.GetValue( columnMap.ColumnNumber );
					SetObjectValue( mappedObject, columnMap, value );
				}

				mappedList.Add( mappedObject );
			}

			return mappedList;
		}

		/// <summary>
		/// Sets the object value.
		/// </summary>
		/// <param name="mappedObject">The mapped object.</param>
		/// <param name="columnMap">The column map.</param>
		/// <param name="value">The value.</param>
		private void SetObjectValue( TMappedType mappedObject, ColumnMap columnMap, object value )
		{
            // don't bother setting the value of a null from the database.
            if( value is DBNull )
            {
                return;
            }

			if( columnMap.Converter != null )
			{
				value = columnMap.Converter.ConvertTo( value, columnMap.ToType );
			}

			if( columnMap.MapType == ColumnMapType.ToField )
			{
				columnMap.Field.SetValue( mappedObject, value );
			}
			else if( columnMap.MapType == ColumnMapType.ToProperty )
			{
				columnMap.Property.SetValue( mappedObject, value, null );
			}
		}
	}
}
